<?php

class SimpleSQLiteDatabase implements Whatsit_Database
{
    private $username;
    protected $db;
    private $userid;

    function __construct($username)
    {
        $this->username = $username;
        try {
                $this->db = new PDO('sqlite:../database/whatsits.sqlite');
        } catch (PDOException $e) {
                error_log('Couldnt connect to database' . $e->getMessage());
                die($e->getMessage());
        }
    }

    function __destruct()
    {
        $this->db = NULL;
    }

    private function addPrefs(&$whatsit_obj)
    {
        $id = $whatsit_obj->getID();
        $sql = "SELECT * FROM preference WHERE instance_id = $id;";
        $result = $this->db->query($sql);
        foreach ($result as $entry) {
                $whatsit_obj->setPref($entry['pref_name'], $entry['pref_value']);
        }	
    }

    private function getPref($instance_id, $pref_name)
    {
        $select = "SELECT pref_value FROM preference, display_instance, user ";
        $select .= "WHERE preference.instance_id = $instance_id ";
        $select .= "	AND preference.pref_name = '$pref_name' ";
        $select .= "	AND preference.instance_id = display_instance.instance_id ";
        $select .= "	AND display_instance.user_id = user.user_id AND user.user_name = '{$this->username}' ";
        $select .= "LIMIT 0,1;";
        $results = $this->db->query($select);
        if ($results->rowCount() == 1) {
                $row = $results->fetch();
                return $row['pref_value'];
        } else {
                return false;
        }
    }

    /**
    * gets an array of whatsits for a particular user ane pane
    * @param string $user_id id of the user
    * @param string $pane_name the name of the pane.  could be "right" or "left" or "center" depending on your system's configuration
    * @returns an array of urls of whatsits in the pane from top to bottom in order
    */
    public function getWhatsits($pane_name)
    {
        $whatsit_array = array();
        $sql = "SELECT whatsit.*, strftime('%s',whatsit.cache_ts) AS cache_create_time, display_instance.position, display_instance.instance_id ";
        $sql .= "FROM whatsit, display_instance, pane, user ";
        $sql .= "WHERE display_instance.whatsit_id = whatsit.whatsit_id ";
        $sql .= "	AND display_instance.pane_id = pane.pane_id ";
        $sql .= "	AND display_instance.user_id = user.user_id ";
        $sql .= "	AND user.user_name = '{$this->username}' ";
        $sql .= "	AND pane.pane_name = '$pane_name' ";
        $sql .= "ORDER BY display_instance.position DESC;";
        $result = $this->db->query($sql);
        foreach ($result as $entry) {
            $whatsit = new Whatsit((int)$entry['instance_id'], $entry['url']);
            if (isset($entry['cache']) && strlen($entry['cache']) > 10) {
                if (time() > ((int)$entry['cache_create_time'] + (int)$entry['cache_life'])) {
                    $whatsit->setXMLText($entry['cache']);
                } else {
                    //insert code here for refreshing cache	
                }
            }
            if (isset($entry['title'])) {
                $whatsit->setTitle($entry['title']);
            }
            $this->addPrefs($whatsit);
            $whatsit_array[] = $whatsit;
        }
        return $whatsit_array;
    }

    public function setPref($id, $pref_name, $pref_value)
    {
        if ($this->getPref($id, $pref_name) !== false) {
            $sql = "UPDATE preference SET pref_value = '$pref_value' WHERE instance_id = $id AND pref_name = '$pref_name';";
        } else {
            $sql = "INSERT INTO preference (instance_id, pref_name, pref_value) VALUES ($id, '$pref_name', '$pref_value');";
        }
        $res = $this->db->exec($sql);
        return ($res > 0);
    }

    private function getPaneId($pane_name)
    {
        $sql = "SELECT pane_id FROM pane WHERE pane_name = '$pane' LIMIT 0,1;";
        $result = $this->db->query($sql);
        if ($result && count($result) == 1) {
            $row = $result->fetch();
            return (int)$row['pane_id'];
        } else {
            return false;
        }
    }

    private function getUserId()
    {
        if (isset($this->userid)) return $this->userid;
        $sql = "SELECT user_id FROM user WHERE user_name = '{$this->username}' LIMIT 0,1;";
        $result = $this->db->query($sql);
        if ($result && $result->rowCount() == 1) {
            $row = $result->fetch();
            return $this->userid = (int)$row['user_id'];
        } else {
            return false;
        }
    }

    public function addWhatsit($url, $pane)
    {
        $select = "SELECT * FROM whatsit WHERE url = '$url' LIMIT 0,1;";
        $result = $this->db->query($select);
        if ($result && $result->rowCount() > 0) {
            $row = $result->fetch();
            $id = (int)$row['whatsit_id'];
        } else {
            $insert = "INSERT INTO whatsit (url) VALUES ('$url');";
            $result = $this->db->exec($insert);
            $id = $this->db->lastInsertId();
        }
        $pane_id = $this->getPaneId($pane);
        $user_id = $this->getUserId();
        $insert = "INSERT INTO display_instance (whatsit_id, user_id, pane_id, position) ";
        $insert .= "VALUES ($id, $user_id, $pane_id, 0);";
        $this->db->exec($insert);
        return $this->db->lastInsertId();
    }

    public function moveWhatsit($whatsit_id, $from_pane, $to_pane, $after_whatsit_id)
    {
        $to_pane_id = $this->getPaneId($to_pane);
        $user_id = $this->getUserId();
        $sqlselect = "SELECT position FROM display_instance ";
        $sqlwhere = "WHERE whatsit_id = $after_whatsit_id ";
        $sqlconstraints = "AND user_id = $user_id AND pane_id = $to_pane_id ";
        $sqllimit = "LIMIT 0,1;";
        $result = $this->db->query($sqlselect.$sqlwhere.$sqlconstraints.$sqllimit);
        $row = $result->fetch();
        $position_top_value = $row['position'];
        $sqlwhere = "WHERE position < $position_top_value ";
        $sqllimit = "ORDER BY position DESC LIMIT 0,1";
        $result = $this->db->query($sqlselect.$sqlwhere.$sqlconstraints.$sqllimit);
        $row = $result->fetch();
        $position_bottom_value = $row['position'];
        $new_position_value = ($position_top_value + $position_bottom_value)/2;
        $update = "UPDATE display_instance SET position = $new_position_value WHERE instance_id = $whatsit_id;";
        $this->db->exec($update);
    }

    public function getWhatsit($id)
    {
        $sql = "SELECT whatsit.*, strftime('%s',whatsit.cache_ts) AS cache_create_time, display_instance.instance_id ";
        $sql .= "FROM whatsit, display_instance, pane, user ";
        $sql .= "WHERE display_instance.whatsit_id = whatsit.whatsit_id ";
        $sql .= "	AND display_instance.pane_id = pane.pane_id ";
        $sql .= "	AND display_instance.user_id = user.user_id ";
        $sql .= "	AND user.user_name = '{$this->username}' ";
        $sql .= "	AND display_instance.instance_id = $id ";
        $sql .= "LIMIT 0,1;";
        try {
            $result = $this->db->query($sql);
        } catch (PDOException $e) {
            die ($e->getMessage());	
        }
        $entry = $result->fetch();
        $whatsit = new Whatsit((int)$entry['instance_id'], $entry['url']);
        if (isset($entry['cache']) && strlen($entry['cache']) > 10) {
            if (time() > ((int)$entry['cache_create_time'] + (int)$entry['cache_life'])) {
                $whatsit->setXMLText($entry['cache']);
            } else {
                //insert code here for refreshing cache	
            }
        }
        if (isset($entry['title'])) {
            $whatsit->setTitle($entry['title']);
        }
        $this->addPrefs($whatsit);
        return $whatsit;
    }
}